package cn.ziyoufa.smart.smartschool.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;

import cn.ziyoufa.smart.smartschool.application.PasisApplication;
import cn.ziyoufa.smart.smartschool.constant.Constant;

/**
 * 创建人 ： 赵泽东
 * 创建时间：2018/9/5
 * 数据库操作
 * @param
 * @param
 */
public class DBHelper extends SQLiteOpenHelper {

    //当前实例
    private static DBHelper helper = null;
    //字段公共类型
    public static final  String INTEGER_TYPE = "Integer";
    public static final String TEXT_TYPE = " TEXT";
    public static final String DESC = " DESC";
    //表公共字段
    public static final String ID="_id";
    public static final String TIME = "created_at";
    //计划任务开关机记录表
    public static final String SCHEME_ID = "ele_shutdown_id";
    public static final String SCHEME_WEEK = "ele_shutdown_week";
    public static final String SCHEME_WEEK_NUM = "ele_shutdown_week_num";
    public static final String SCHEME_STIME = "ele_shutdown_stime";
    public static final String SCHEME_STIME_H = "ele_shutdown_stime_h";
    public static final String SCHEME_STIME_M = "ele_shutdown_stime_m";
    public static final String SCHEME_ETIME = "ele_shutdown_etime";
    //开关机日志记录表
    public static final String SHUT_S_WEEK = "ele_shutdown_s_week";
    public static final String SHUT_E_WEEK = "ele_shutdown_e_week";
    public static final String SHUT_STIME = "ele_shutdown_stime";
    public static final String SHUT_ETIME = "ele_shutdown_etime";
    public static final String REDUCE_TIME = "ele_reduce_time";



    //表名
    public static final String SCHEME_TABLE = "ps_ele_shutdown";
    public static final String SHUT_TABLE = "ps_shutdown_log";
    public static final String POWER_ON_TABLE = "ps_power_on_record";//开机时间记录表

    public DBHelper() {
        super(PasisApplication.getInstance(), Constant.DB_NAME, null, Constant.DB_VERSION);
    }

    public DBHelper(Context context) {
        super(context, Constant.DB_NAME, null, Constant.DB_VERSION);
    }

    /**
     * 当两个并发线程访问同一个对象object中的这个synchronized(this)同步代码块时，一个时间内只能有一个线程得到执行。
     * 另一个线程必须等待当前线程执行完这个代码块以后才能执行该代码块
     * @return context
     */
    public static synchronized DBHelper getInstance(){
        if(helper == null){
            helper = new DBHelper();
        }
        return helper;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.beginTransaction();
        try {
            createAllTables(db);
            createShutdownLogTables(db);
            createPowerOnRecordTables(db);
            db.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            db.endTransaction();
        }
    }

    private void createAllTables(SQLiteDatabase db) {
        /**
         * 创建计划任务开关机记录表
         */
        String[] cloumns = new String[]{SCHEME_ID + TEXT_TYPE, SCHEME_WEEK + TEXT_TYPE, SCHEME_STIME + TEXT_TYPE,
                SCHEME_ETIME + TEXT_TYPE,SCHEME_STIME_H + TEXT_TYPE, SCHEME_STIME_M + TEXT_TYPE
                , SCHEME_WEEK_NUM + TEXT_TYPE};
        createTable(db, SCHEME_TABLE, cloumns);
    }

    private void createShutdownLogTables(SQLiteDatabase db) {
        /**
         * 创建开关机日志表
         */
        String[] cloumns = new String[]{SHUT_S_WEEK + TEXT_TYPE, SHUT_E_WEEK + TEXT_TYPE, SHUT_STIME + TEXT_TYPE,
                SHUT_ETIME + TEXT_TYPE,REDUCE_TIME + TEXT_TYPE};
        createTable(db, SHUT_TABLE, cloumns);
    }

    private void createPowerOnRecordTables(SQLiteDatabase db) {
        /**
         * 创建开机记录表
         */
        String[] cloumns = new String[]{};
        createTable(db, POWER_ON_TABLE, cloumns);
    }

    /**
     * @param sqliteDatabase
     * @param table          要创建的数据表名
     * @param columns        列名
     */
    private void createTable(SQLiteDatabase sqliteDatabase, String table, String[] columns) {
        String createTable = "create table if not exists ";
        String primaryKey = " Integer primary key autoincrement";
        String text = " text";
        char leftBracket = '(';
        char rightBracket = ')';
        char comma = ',';
        int stringBufferSize = 170;
        StringBuffer sql = new StringBuffer(stringBufferSize); // StringBuffer的效率会更高一些
        sql.append(createTable).append(table).append(leftBracket).append(ID).append(primaryKey).append(comma);
        for (String column : columns) {
            sql.append(column);
            sql.append(comma);
        }
        sql.append(TIME).append(text).append(rightBracket);
        try {
            sqliteDatabase.execSQL(sql.toString());
        } catch (Exception e) {
            e.getMessage();
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //根据老的数据库版本名来升级数据库,增量升级
        switch (oldVersion) {
            case 1:
                //此处不应该有break; 保证版本跳跃时也可以全部更新，例如:由oldVersion为1直接到version 5
            case 2:
                //老版本为2时,说明一定执行过了老版本为1时的更新,再执行以后所有版本的更新即可,不需要使用网上的那种循环的方式。
            case 3:

            default:
                break;
        }
    }

    /**
     * 插入数据
     *
     * @param table
     * @param nullColumnHack
     * @param values
     * @return
     */
    public synchronized long insert(final String table, final String nullColumnHack, final ContentValues values) {
        SQLiteDatabase database = null;
        try {
            database = getWritableDatabase();
            return database.insert(table, nullColumnHack, values);
        } catch (Exception e) {
            return -1;
        } finally {
            closeDatabase(database);
        }
    }
    /**
     * 批量插入数据，显示使用事物,事物的效率在批量插入时非常的明显
     */
    public synchronized boolean insert(final String table, final String nullColumnHack,
                                       final ArrayList<ContentValues> values) {
        boolean result = true;
        SQLiteDatabase database = null;
        try {
            database = getWritableDatabase();
            database.beginTransaction();
            for (ContentValues value : values) {
                if (database.insert(table, nullColumnHack, value) < 0) {
                    result = false;
                    break;
                }
            }
            if (result) {
                database.setTransactionSuccessful();
            }
        } catch (Exception e) {
            return false;
        } finally {
            database.endTransaction();
            closeDatabase(database);
        }
        return result;
    }
    /**
     * 删除数据
     *
     * @param table
     * @param whereClause //删除条件
     * @param whereArgs   //删除条件值
     * @return
     */
    public int delete(final String table, final String whereClause, final String[] whereArgs) {
        SQLiteDatabase database = null;
        try {
            database = getWritableDatabase();
            return database.delete(table, whereClause, whereArgs);

        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        } finally {
            closeDatabase(database);
        }
    }
    /**
     * 更新数据
     *
     * @param table
     * @param values
     * @param whereClause
     * @param whereArgs
     * @return
     */
    public int update(final String table, final ContentValues values, final String whereClause,
                      final String[] whereArgs) {
        SQLiteDatabase database = null;
        try {
            database = getWritableDatabase();
            return database.update(table, values, whereClause, whereArgs);
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        } finally {
            closeDatabase(database);
        }
    }
    /**
     * 关闭数据库
     */
    public void closeDatabase(SQLiteDatabase db) {
        if (db != null && db.isOpen()) {
            db.close();
        }
    }

    /**
     * 判断某张表是否存在
     * @param tabName 表名
     * @return
     */
    public boolean tabIsExist(String tabName){
        boolean result = false;
        if(tabName == null){
            return false;
        }
        SQLiteDatabase db = null;
        Cursor cursor = null;
        try {
            db = this.getReadableDatabase();//此this是继承SQLiteOpenHelper类得到的
            String sql = "select count(*) as c from sqlite_master where type ='table' and name ='"+tabName.trim()+"' ";
            cursor = db.rawQuery(sql, null);
            if(cursor.moveToNext()){
                int count = cursor.getInt(0);
                if(count>0){
                    result = true;
                }
            }

        } catch (Exception e) {
            // TODO: handle exception
        }
        return result;
    }

}
